package com.prelearning.at_springboot.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.extension.service.IService;
import com.prelearning.at_springboot.entity.Published;
import com.prelearning.at_springboot.service.IPublishedService;
import com.prelearning.at_springboot.service.impl.PublishedServiceImpl;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.Objects;
import java.util.Set;

/**
 * <p>
 *  json转excel工具
 * </p>
 *
 * @author cgr
 * @since 2022-04-28
 */
public class ExcelUtil {

    //JSONArray转Excel表格
    public static void jsonToExcel(JSONArray jsonArray, String excelFilePath) {
        try{
            //根据json数组生成excel页
            HSSFWorkbook sheets = jsonToExcel(jsonArray);
            // 文件下载
            File excelFile = new File(excelFilePath);
            sheets.write(excelFile);
            sheets.close();
        } catch (Exception ignored) {

        }
    }

    //遍历jsonArray将json转为excel
    private static HSSFWorkbook jsonToExcel(JSONArray jsonArray) {
        for(int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            String status=jsonObject.getString("sign_in_status");
            String statusToString = null;
            
            if(Objects.equals(status, "0")){ statusToString="未签到";
            }else if(Objects.equals(status, "1")){statusToString="已签到";
            }else if(Objects.equals(status, "2")){statusToString="请假";
            }else if(Objects.equals(status, "3")){statusToString="迟到";}
            
            jsonObject.remove("sign_in_status");
            jsonObject.put("sign_in_status",statusToString);
        }

        Set<String> keys = null;
        // 创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建HSSFSheet对象
        HSSFSheet sheet = wb.createSheet("sheet0");

        int roleNo = 0;
        int rowNo = 0;
        // 创建HSSFRow对象
        HSSFRow row = sheet.createRow(roleNo++);
        // 创建HSSFCell对象
        // 创建表格标题
        keys = jsonArray.getJSONObject(0).keySet();
        for (String s : keys) {
            HSSFCell cell = row.createCell(rowNo++);
            cell.setCellValue(s);
        }
        rowNo = 0;

        for (int i = 0; i < jsonArray.size(); i ++) {
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            row = sheet.createRow(roleNo++);
            for (String s : keys) {
                HSSFCell cell = row.createCell(rowNo++);
                cell.setCellValue(jsonObject.getString(s));
            }
            rowNo = 0;
        }
        return wb;
    }

//    public static void main(String[] args){
//        JSONArray jsonArray=JSONArray.parseArray("[{\"user_id\": \"ChenGuanRu\", \"user_name\": \"陈关如\", \"sign_in_time\": \"\", \"school_number\": \"201808061234\", \"sign_in_status\": 0}, {\"user_id\": \"lisi\", \"user_name\": \"李四\", \"sign_in_time\": \"\", \"school_number\": \"201808061234\", \"sign_in_status\": 0}, {\"user_id\": \"zhangsan\", \"user_name\": \"张三\", \"sign_in_time\": \"\", \"school_number\": \"\", \"sign_in_status\": 0}, {\"user_id\": \"wangwu\", \"user_name\": \"王五\", \"sign_in_time\": \"\", \"school_number\": \"201808061234\", \"sign_in_status\": 0}]");
//        jsonToExcel(jsonArray, "D:\\idea-projects\\at_springboot\\src\\main\\resources\\static\\"+"c20220507004431827"+".xls");
//    }

    //excel传输文件流
    public static void readExcelToIO(Published published, HttpServletResponse response) throws IOException {
        String signInId=published.getSignInId();
        String result=published.getResult();
        JSONArray jsonArray=JSONArray.parseArray(result.substring(10,result.length()-1));
        String fileName="D:\\idea-projects\\at_springboot\\src\\main\\resources\\static\\"+signInId+".xls";
        jsonToExcel(jsonArray,fileName);

        //判断是否为excel类型文件
        if(!fileName.endsWith(".xls")) {
            throw new RuntimeException("所传入文件不是Excel文件！");
        }
        //读取Excel文件
        File excelFile = new File(fileName.trim());
        InputStream inputStream = new FileInputStream(excelFile);
        // 构造 HSSFWorkbook 对象，strPath 传入文件路径
        Workbook workbook = null;
        //获取Excel工作薄
        if (excelFile.getName().endsWith("xls")) {
            workbook = new HSSFWorkbook(inputStream);
        }
        if (workbook == null) {
            throw new RuntimeException("Excel文件有问题,请检查！");
        }
        // 读取第一个sheet页表格内容
        Sheet sheet = workbook.getSheetAt(0);
        OutputStream os = response.getOutputStream();

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        // 返回数据到输出流对象中
        os.write(baos.toByteArray());
        os.flush();
        os.close();
    }

}
